IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientProcudureDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientProcudureDetail]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetPatientProcudureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[PatientProcudureDetail] (
   ID   Varchar(30),
   [CRNumber]     Varchar(50),
   [ProcedureName]     Varchar(50),
   [ProcedureCode]     Varchar(50),
   [ProcedureDate]     Datetime,
   [ProcedureTime]     Varchar(50),
   [Remarks]     Varchar(50),
   [Consultant]     Varchar(50),
   [ReportId]     Varchar(50),
   [Billing]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientProcudureDetail TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatientProcudureDetail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatientProcudureDetail
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_GetPatientProcudureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetPatientProcudureDetail
@vchID VARCHAR(30)
AS
   SELECT
       ID,
       CRNumber,
       ProcedureName,
       ProcedureCode,
       ProcedureDate,
       ProcedureTime,
       Remarks,
       Consultant,
       ReportId,
       Billing
   FROM PatientProcudureDetail
   WHERE ID=@vchID
GO

GRANT EXEC ON dbo.SP_GetPatientProcudureDetail TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatientProcudureDetail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatientProcudureDetail
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetPatientProcudureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetPatientProcudureDetail
@vchID Varchar(30),
@vchCRNumber    Varchar(50),
@vchProcedureName    Varchar(50),
@vchProcedureCode    Varchar(50),
@dtProcedureDate    Datetime,
@vchProcedureTime    Varchar(50),
@vchRemarks    Varchar(50),
@vchConsultant    Varchar(50),
@vchReportId    Varchar(50),
@vchBilling    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     ID
   FROM PatientProcudureDetail
   WHERE   (ID = @vchID)
   )

   BEGIN
   INSERT INTO PatientProcudureDetail(
       ID,
       CRNumber,
       ProcedureName,
       ProcedureCode,
       ProcedureDate,
       ProcedureTime,
       Remarks,
       Consultant,
       ReportId,
       Billing
  )
   VALUES (
      @vchID,
       @vchCRNumber,
       @vchProcedureName,
       @vchProcedureCode,
       @dtProcedureDate,
       @vchProcedureTime,
       @vchRemarks,
       @vchConsultant,
       @vchReportId,
       @vchBilling
   )
   END
ELSE  IF EXISTS(SELECT     ID
   FROM PatientProcudureDetail
   WHERE   (ID = @vchID)
   )

   BEGIN
   UPDATE PatientProcudureDetail
   SET
       CRNumber = @vchCRNumber,
       ProcedureName = @vchProcedureName,
       ProcedureCode = @vchProcedureCode,
       ProcedureDate = @dtProcedureDate,
       ProcedureTime = @vchProcedureTime,
       Remarks = @vchRemarks,
       Consultant = @vchConsultant,
       ReportId = @vchReportId,
       Billing = @vchBilling
  WHERE (ID = @vchID)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetPatientProcudureDetail for Loan# : ' + CAST (@vchID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetPatientProcudureDetail TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeletePatientProcudureDetail') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeletePatientProcudureDetail
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_DeletePatientProcudureDetail
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeletePatientProcudureDetail
@vchID VARCHAR(30)
AS
   DELETE FROM PatientProcudureDetail  WHERE   (ID = @vchID)

GRANT EXEC ON dbo.SP_DeletePatientProcudureDetail TO hms_usr
GO

